package com.us.usws.dao;

import java.util.Date;
import java.util.List;

import net.iwsh.core.db.DBExecutor;
import net.iwsh.core.db.ext.DialectSyntaxParser;
import net.iwsh.core.db.ext.SQLWithParams;
import net.iwsh.core.exception.DBException;

import com.us.usws.common.CommonUtils;
import com.us.usws.model.domain.channel.Channel;
import com.us.usws.model.flat.channel.DOChannel;

public class ChannelDAO {

	/**
	 * Query SQLs
	 */
	private static final String SQL_GET_TOTAL_COUNT = "select count(*) from hv_channel";
	private static final String SQL_GET_ALL_CHANNELS = "select * from hv_channel order by seq_num";
	private static final String SQL_GET_CHANNEL_BY_PK = "select * from hv_channel where uid = ?";
	private static final String SQL_GET_HOMEPAGE_CHANNELS = "select * from hv_channel where is_idx_channel=? and is_valid='Y' order by seq_num";
	private static final String SQL_GET_CHNNELS_BY_PARENT_AND_ROLE = "select * from hv_channel where is_valid='Y' and parent_uid=? and target_role & ? = ? order by seq_num";

	private static final String SQL_FORM_CHANNEL_VIDEO_LINK_MAX_SEQ_NUM = "select max(seq_num) from hv_channel_video_link where channel_uid=?";
	/**
	 * CUR SQLs
	 */
	private static final String SQL_INSERT_CHANNEL = "insert into hv_channel(uid,is_idx_channel,name,parent_uid,picture,channel_type_dsid,ui_type,is_valid,seq_num,target_role,description,create_by_uid,create_date,last_upd_by_uid,last_upd_date,relate_cat,video_quality,video_from,metting_type,metting_status, plate_picture) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

	private static final String SQL_UPDATE_CHANNEL = "update hv_channel set is_idx_channel=?, name=?, parent_uid=?, picture=?, channel_type_dsid=?, ui_type=?, is_valid=?, seq_num=?, target_role=?, description=?, last_upd_by_uid=?, last_upd_date=?, relate_cat=?, video_quality=?, video_from=?, metting_type=?, metting_status=?, plate_picture=? where uid=?";

	private static final String SQL_UPDATE_CHANNEL_IS_VALID = "update hv_channel set is_valid=? where uid=?";
	private static final String SQL_DEL_CHANNEL = "delete from hv_channel where uid=?";
	private static final String SQL_GET_TOP_CHANNELS = "select * from hv_channel where parent_uid='/'";
	private static final String SQL_GET_TOP_CHANNELS_BY_TYPE = "select * from hv_channel where parent_uid='/' and channel_type_dsid=?";
	
	private static final String SQL_INSERT_CHANNEL_VIDEO_LINK = "insert into hv_channel_video_link(channel_uid, video_uid, seq_num, is_hot, create_by_uid, create_date) values (?,?,?,?,?,?)";

	private static final String SQL_DEL_CHANNEL_VIDEO_LINK = "delete from hv_channel_video_link where channel_uid=? and video_uid=?";
	
	private static final String SQL_UPDATE_CHANNEL_VIDEO_HOT = "update hv_channel_video_link set is_hot=? where channel_uid=? and video_uid=?";
	
	private static final String SQL_UPDATE_CHANNEL_VIDEO_SEQNUM = "update hv_channel_video_link set seq_num=? where channel_uid=? and video_uid=?";
	/**
	 * DBExeutor
	 */
	private DBExecutor dbe = new DBExecutor();

	/**
	 * Get Count.
	 */
	public int getTotalCount() {
		return dbe.getCount(SQL_GET_TOTAL_COUNT);
	}

	/**
	 * Get all list.
	 */
	public List<Channel> getAllChannels() {
		return dbe.list(SQL_GET_ALL_CHANNELS, Channel.class);
	}

	/**
	 * Get all list by pagination.
	 */
	public List<Channel> getChannels(int start, int end) {
		SQLWithParams swp = DialectSyntaxParser.getParser().getPaginationSQLWithParams(SQL_GET_ALL_CHANNELS, null, start, end);
		return dbe.list(swp.getSql(), Channel.class, swp.getParams());
	}

	/**
	 * Get Channel by primary key.
	 */
	public Channel getChannel(String uid) {
		Channel c = dbe.load(SQL_GET_CHANNEL_BY_PK, Channel.class, uid);
		return c;
	}

	public List<Channel> getChannels(boolean isMain) {
		return dbe.list(SQL_GET_HOMEPAGE_CHANNELS, Channel.class, CommonUtils.toYorN(isMain));
	}

	public List<Channel> getChannelsByParentWithTargetLogic(String parentUid, int roleMode) {
		return dbe.list(SQL_GET_CHNNELS_BY_PARENT_AND_ROLE, Channel.class, parentUid, roleMode, roleMode);
	}

	public List<Channel> getTopChannelsByTargetRole(int targetRole) {
		return dbe.list(SQL_GET_CHNNELS_BY_PARENT_AND_ROLE, Channel.class, "/", targetRole, targetRole);
	}

	public List<DOChannel> getTopChannels() {
		return dbe.list(SQL_GET_TOP_CHANNELS, DOChannel.class);
	}

	public List<DOChannel> getTopChannelsByType(int typeDsid) {
		return dbe.list(SQL_GET_TOP_CHANNELS_BY_TYPE, DOChannel.class, typeDsid);
	}

	/**
	 * Insert one row.
	 */
	public void insertChannel(DOChannel channel) {
		dbe.execute(SQL_INSERT_CHANNEL, channel.getUid(), channel.getIsIdxChannel(), channel.getName(), channel.getParentUid(),
				channel.getPicture(), channel.getChannelTypeDsid(), channel.getUiType(), channel.getIsValid(), channel.getSeqNum(),
				channel.getTargetRole(), channel.getDescription(), channel.getCreateByUid(), channel.getCreateDate(),
				channel.getLastUpdByUid(), channel.getLastUpdDate(), channel.getRelateCat(), channel.getVideoQuality(),
				channel.getVideoFrom(), channel.getMettingType(), channel.getMettingStatus(), channel.getPlatePicture());
	}

	/**
	 * Update by PK.
	 */
	public void updateChannel(DOChannel channel) {
		dbe.execute(SQL_UPDATE_CHANNEL, channel.getIsIdxChannel(), channel.getName(), channel.getParentUid(), channel.getPicture(),
				channel.getChannelTypeDsid(), channel.getUiType(), channel.getIsValid(), channel.getSeqNum(), channel.getTargetRole(),
				channel.getDescription(), channel.getLastUpdByUid(),
				channel.getLastUpdDate(), channel.getRelateCat(), channel.getVideoQuality(), channel.getVideoFrom(),
				channel.getMettingType(), channel.getMettingStatus(), channel.getPlatePicture(), channel.getUid());
	}

	/**
	 * batch update more channel
	 * @param channels
	 */
	public void updateChannels(List<DOChannel> channels) {
		dbe.startTransaction();
		try {
			for (DOChannel channel : channels) {
				dbe.executeBatch(SQL_UPDATE_CHANNEL, channel.getIsIdxChannel(), channel.getName(), channel.getParentUid(), channel.getPicture(),
						channel.getChannelTypeDsid(), channel.getUiType(), channel.getIsValid(), channel.getSeqNum(), channel.getTargetRole(),
						channel.getDescription(), channel.getLastUpdByUid(),
						channel.getLastUpdDate(), channel.getRelateCat(), channel.getVideoQuality(), channel.getVideoFrom(),
						channel.getMettingType(), channel.getMettingStatus(), channel.getPlatePicture(), channel.getUid());
			}
		} catch (Exception e) {
			dbe.rollbackTransaction();
			e.printStackTrace();
			throw new DBException("excute failure!!");
		} finally {
			dbe.commitTransaction();
		}
	}

	/**
	 * Update is valid flag by PK.
	 */
	public void updateIsValid(String isValid, String uid) {
		dbe.execute(SQL_UPDATE_CHANNEL_IS_VALID, isValid, uid);
	}

	/**
	 * Delete row by PK.
	 */
	public void deleteChannel(String uid) {
		dbe.execute(SQL_DEL_CHANNEL, uid);
	}
	
	/**
	 * add channel video
	 * @param uids
	 * @param channelUid
	 * @param createByUid
	 */
	public void addChannelVideo(String[] channelVideoUids, String channelUid, String createByUid) {
		dbe.startTransaction();
		try {
			Date now = new Date(System.currentTimeMillis());
			int seqNum = dbe.getCount(SQL_FORM_CHANNEL_VIDEO_LINK_MAX_SEQ_NUM, channelUid);
			for (String videoUid : channelVideoUids) {
				seqNum++;
				dbe.executeBatch(SQL_INSERT_CHANNEL_VIDEO_LINK, channelUid, videoUid, seqNum, "N", createByUid, now);
			}
		} catch (Exception e) {
			dbe.rollbackTransaction();
			e.printStackTrace();
			throw new DBException("excute failure!!");
		} finally {
			dbe.commitTransaction();
		}
	}
	
	/**
	 * delete channel video
	 * @param uids
	 * @param channelUid
	 */
	public void deleteChannelVideo(String[] channelVideoUids, String channelUid) {
		dbe.startTransaction();
		try {
			for (String videoUid : channelVideoUids) {
				dbe.executeBatch(SQL_DEL_CHANNEL_VIDEO_LINK, channelUid, videoUid);
			}
		} catch (Exception e) {
			dbe.rollbackTransaction();
			e.printStackTrace();
			throw new DBException("excute failure!!");
		} finally {
			dbe.commitTransaction();
		}
	}
	
	/**
	 * recomment channel video
	 * @param uids
	 * @param channelUid
	 */
	public void recommentChannelVideo(String[] channelVideoUids, String channelUid, String hot) {
		dbe.startTransaction();
		try {
			for (String videoUid : channelVideoUids) {
				dbe.executeBatch(SQL_UPDATE_CHANNEL_VIDEO_HOT, hot, channelUid, videoUid);
			}
		} catch (Exception e) {
			dbe.rollbackTransaction();
			e.printStackTrace();
			throw new DBException("excute failure!!");
		} finally {
			dbe.commitTransaction();
		}
	}
	
	/**
	 * top channel video
	 * @param uids
	 * @param channelUid
	 */
	public void topChannelVideo(String[] channelVideoUids, String channelUid, int[] seqNums) {
		dbe.startTransaction();
		try {
			for (int i=0; i<channelVideoUids.length; i++) {
				dbe.executeBatch(SQL_UPDATE_CHANNEL_VIDEO_SEQNUM, seqNums[i], channelUid, channelVideoUids[i]);
			}
		} catch (Exception e) {
			dbe.rollbackTransaction();
			e.printStackTrace();
			throw new DBException("excute failure!!");
		} finally {
			dbe.commitTransaction();
		}
	}

}
